import requests
from bs4 import BeautifulSoup
import sqlite3

# 创建数据库连接
conn = sqlite3.connect('music.db')
cursor = conn.cursor()

# 创建music250表
cursor.execute('''CREATE TABLE IF NOT EXISTS music250 (
                id INTEGER PRIMARY KEY,
                music_url TEXT,
                cover_url TEXT,
                music_name TEXT,
                original_name TEXT,
                rating REAL,
                votes INTEGER,
                summary TEXT,
                info TEXT)''')

# 请求豆瓣音乐250排行榜页面
url = 'https://music.douban.com/top250'
response = requests.get(url)
soup = BeautifulSoup(response.text, 'html.parser')

# 爬取每部作品的信息
music_list = soup.find_all('tr', class_='item')
for music in music_list:
    # 获取音乐链接
    music_url = music.find('a')['href']

    # 获取封面图链接
    cover_url = music.find('img')['src']

    # 获取音乐中文名字
    music_name = music.find('div', class_='pl2').find('a').text.strip()

    # 获取音乐原名
    original_name = music.find('div', class_='pl2').find('span', class_='pl').text.strip().strip("()")

    # 获取音乐评分
    rating = float(music.find('span', class_='rating_nums').text)

    # 获取评价人数
    votes = int(music.find('span', class_='pl').find_all('span')[-1].text.strip("人评价"))

    # 获取作品内容简介
    summary = music.find('p', class_='quote').text.strip()

    # 获取作品信息
    info = music.find('p', class_="").text.strip()

    # 将数据插入数据库
    cursor.execute(
        "INSERT INTO music250 (music_url, cover_url, music_name, original_name, rating, votes, summary, info) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
        (music_url, cover_url, music_name, original_name, rating, votes, summary, info))

# 提交事务并关闭数据库连接
conn.commit()
conn.close()
